package com.example.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.bean.User;

public class DBHelper {

	public final static String driver = "com.mysql.jdbc.Driver";
	public final static String url = "jdbc:mysql://localhost:3306/test";
	public final static String user = "root";
	public final static String password = "root";

	public static void query() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			Statement smts = conn.createStatement();
			String sql = "select id, name, sex, age, tel, address from user";
			ResultSet rs = smts.executeQuery(sql);
			System.out.println("id\tname\tsex\tage\ttel\taddress");
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String sex = rs.getString("sex");
				int age = rs.getInt("age");
				String tel = rs.getString("tel");
				String address = rs.getString("address");
				
				System.out.println(id + "\t" + name + "\t" + sex + "\t" + age + "\t" + tel + "\t" + address);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static List<User> query(String sql) {
		List list = new ArrayList();
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			Statement smts = conn.createStatement();
			//String sql = "select id, name, sex, age, tel, address from user";
			ResultSet rs = smts.executeQuery(sql);

			while (rs.next()) {
				User user = new User();
				list.add(user);
				
				long id = rs.getLong("id");
				String name = rs.getString("name");
				int sex = rs.getInt("sex");
				int age = rs.getInt("age");
				String tel = rs.getString("tel");
				String address = rs.getString("address");
				
				user.setId(id);
				user.setAddress(address);
				user.setAge(age);
				user.setName(name);
				user.setSex(sex);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}

	public static void main(String[] args) {
		//query();
		String sql = "select id, name, sex, age, tel, address from user";
		List<User> list = query(sql);
		System.out.println("id\tname\tsex\tage\ttel\taddress");
		if (list != null && list.size()>0) {
			for (User user : list) {
				System.out.println(user.getId() + "\t" + user.getName() + "\t" + user.getSex() 
						+ "\t" + user.getAge() + "\t" + user.getTel() + "\t" + user.getAddress());
			}
			
		}
	}
}
